-- This file has demos for data-type precedence and conversion.
-- © Erland Sommarskog 2020.

USE SQLTitbits;
go
-- This table mimicks a configuration table. Many systems needs a lot of
-- configuration values. A "proper" design of a table like this is one column 
-- per configuration option, but that is not tenable in practice, so most
-- people have one row per option. In this version we have different
-- columns depending on the data type for the config option.
SELECT * FROM dbo.ConfigValues;
go
-- The intention with this stored procedure is to return one option and
-- the value of that option, in the same column no matter the data type.
CREATE OR ALTER PROCEDURE dbo.GetConfig @id int AS
  SELECT id, name, type, 
         CASE type
              WHEN 'int'  THEN intval
              WHEN 'str'  THEN strval
              WHEN 'date' THEN dateval
              WHEN 'dtm'  THEN datetimeval
--              WHEN 'guid' THEN guidval
        END AS Value
  FROM  dbo.ConfigValues
  WHERE id = @id;
go
-- But it does not work well. Run these calls, and see how it only gets worse.
EXEC dbo.GetConfig 5;
EXEC dbo.GetConfig 4;
EXEC dbo.GetConfig 1;
EXEC dbo.GetConfig 2;
-- And at this point uncomment the row for the guid column and try to re-
-- create the procedure.
go

-- To get a working version, we need to convert to a useful data type.
-- sql_variant is the most general, although not very many use it.
CREATE OR ALTER PROCEDURE dbo.GetConfig @id int AS
  SELECT id, name, type, 
         CASE type
              WHEN 'int'  THEN convert(sql_variant, intval)
              WHEN 'str'  THEN convert(sql_variant, strval)
              WHEN 'date' THEN convert(sql_variant, dateval)
              WHEN 'dtm'  THEN convert(sql_variant, datetimeval)
              WHEN 'guid' THEN convert(sql_variant, guidval)
         END AS Value
  FROM   dbo.ConfigValues
  WHERE  id = @id;
go
-- Now all these run successfully.
EXEC dbo.GetConfig 5;
EXEC dbo.GetConfig 4;
EXEC dbo.GetConfig 1;
EXEC dbo.GetConfig 2;
EXEC dbo.GetConfig 6;

-- In many languages, you would get syntax errors for comparing strings and
-- numbers to each other, but not in SQL. The first statement works, but 
-- the second only compiles but fails when it runs.
SELECT * FROM dbo.ConfigValues WHERE intval = '1234';
SELECT * FROM dbo.ConfigValues WHERE strval = 1234;
go
-- Two ways to work around the error. Convert the number to string, or 
-- use try_convert which returns NULL when conversion fails.
DECLARE @i int = 1234
SELECT * FROM dbo.ConfigValues WHERE strval = convert(varchar(10), @i);
SELECT * FROM dbo.ConfigValues WHERE try_convert(int, strval) = @i;
go
